package com.control;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.db.DBConnect;
import com.model.Score;


public class ScoreControl {
	
	//选课
	public boolean insertScore(Score score){
		DBConnect dbc = new DBConnect();
		String sql = "insert into score(course_id, student_id, point) values(?, ?, ?)";
		dbc.prepareStatement(sql);
		try {	
			dbc.setInt(1,score.getCourse_id());
			dbc.setInt(2,score.getStudent_id());
			dbc.setFloat(3,score.getPoint());
		    dbc.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			dbc.close();
		}
	}
	
	//退课
	public boolean deleteScore(Score score){
		DBConnect dbc = new DBConnect();
		String sql = "delete from score where course_id = ? and student_id = ?";
		dbc.prepareStatement(sql);
		try {	
			dbc.setInt(1,score.getCourse_id());
			dbc.setInt(2,score.getStudent_id());
		    dbc.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			dbc.close();
		}
	}
	
	//更新数据
	public boolean updateScore(Score score){
		DBConnect dbc = new DBConnect();
		String sql = "update score set point = ? where id = ?";
		dbc.prepareStatement(sql);
		try {
			dbc.setFloat(1,score.getPoint());
			dbc.setInt(2,score.getId());
		    dbc.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			dbc.close();
		}
	}
	
	//获取选某门课程的所有学生成绩
	public ArrayList<Score> getAllStudentScore(int course_id) {
		ArrayList<Score> list = new ArrayList<Score>();
		DBConnect dbc = new DBConnect();
		String sql = "select * from score where course_id = ? order by student_id";
		dbc.prepareStatement(sql);
		try {
			dbc.setInt(1,course_id);
			ResultSet rs = dbc.executeQuery();
			while(rs.next()) {
				Score score = new Score();
				score.setCourse_id(rs.getInt("course_id"));
				score.setId(rs.getInt("id"));
				float point = rs.getFloat("point");
				score.setPoint(point);
				score.setStudent_id(rs.getInt("student_id"));
				list.add(score);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			dbc.close();
		}
	}
	
	//获取学生的所有课程成绩
	public ArrayList<Score> getAllCourseScore(int student_id) {
		ArrayList<Score> list = new ArrayList<Score>();
		DBConnect dbc = new DBConnect();
		String sql = "select * from score where student_id = ? order by point desc";
		dbc.prepareStatement(sql);
		try {
			dbc.setInt(1,student_id);
			ResultSet rs =  dbc.executeQuery();
			while(rs.next()) {
				Score score = new Score();
				score.setCourse_id(rs.getInt("course_id"));
				score.setId(rs.getInt("id"));
				score.setPoint(rs.getFloat("point"));
				score.setStudent_id(rs.getInt("student_id"));
				list.add(score);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			dbc.close();
		}
	}

}

